# -*- coding: utf-8 -*-

import os, sys,string
import re
import getopt
import xlrd
import xlwt

#字段定义类
class FieldDef:
    db_fieldname = ''
    field_datatype = ''
    title_fieldname = ''
    col_idx = 0

    def __init__(self):
        self.db_fieldname = ''
        self.field_datatype = ''
        self.title_fieldname = ''
        self.col_idx = 0

# usage: python insert_sql.py 国家资料.xlsx abcde.txt sql_config.txt

opts, args = getopt.getopt(sys.argv[1:], "hi:o:c:", ["help","input=","output=","config="])

def usage():
    print(sys.argv[0] + ' -i input filename(xlsx) -o output filename -t config filename')
    print(sys.argv[0] + ' -h #get help info')



'''
#处理输入文件
input_file：输入文件
data_column_list：数据列名称
sql_insert_head：insert sql 头部
sql_list:输出的sql语句列表
'''
def proc_input_data(input_file, data_column_list, sql_insert_head, sql_list):
    #有效数据列的下标
    data_column_index_list = []
    
    #打开输入文件，注意为xlsx文件
    data = xlrd.open_workbook(input_file)
    #读取sheet1
    table=data.sheet_by_index(0)

    #遍历sheet1的所有数据
    iCols = table.ncols
    
    try:
        #处理标题行
        for j in range(len(data_column_list)):
            field_obj = data_column_list[j]
            title_fieldname = field_obj.title_fieldname
            print('config title field: ' + title_fieldname)
            cur_idx = 0
            bfound = False
            for k in range(iCols):
                #print(table.cell_value(0,k))
                if title_fieldname == table.cell_value(0,k).strip():                        
                    bfound = True
                    cur_idx = k
                    break
            if bfound == True:
                field_obj.col_idx = cur_idx
                print(table.cell_value(0,cur_idx))
            else:
                #指定数据表列未找到
                print('fieldname: ' + title_fieldname + ' not found in title line!')
                return False
        
        for i in range(1,table.nrows):
            #从第2行开始
            #proc data line            
            sql_value = ''
            for j in range(len(data_column_list)):
                #print(i,j)
                field_obj = data_column_list[j]
                field_value = str(table.cell_value(i,field_obj.col_idx))
                if field_obj.field_datatype == 'string':
                    if j == 0:
                        sql_value = sql_value + "'" + field_value + "'"
                    else:
                        sql_value = sql_value + ",'" + field_value + "'"
                elif  field_obj.field_datatype == 'int':
                    if j == 0:
                        sql_value = sql_value + str(int(float(field_value)))
                    else:
                        sql_value = sql_value + "," + str(int(float(field_value)))

                else :
                    if j == 0:
                        sql_value = sql_value + field_value
                    else:
                        sql_value = sql_value + "," + field_value

            sql_value = sql_value + ');'

            #insert sql statement
            sql_line = sql_insert_head + sql_value
            sql_list.append(sql_line)
                        
        return True

    except Exception as e:
        print(e)
        return False
            
      
def parse_line(sLine,confirm_token):
    #seperate symbol
    retValue = ''
    sepstr_list = sLine.split(':')
    if len(sepstr_list) == 2:
        token_value = sepstr_list[0]        
        if token_value == confirm_token:
            retValue = sepstr_list[1].strip()

    return retValue
    
    
def proc_config_file(config_file,column_list):
    #处理配置文件，将需要的数据列存入column_index_list列表中，并返回insert sql语句的头部字符串
    sql_insert = ''
    fhandle = open(config_file, 'r')
    try:
        #读4行
        #first line: tablename
        line = fhandle.readline()
        tablename = parse_line(line,'tablename')
        if tablename == '':
            #配置数据异常
            print('error in the first line : ' + line)
            return ''
                
        #second line: db_fields
        line = fhandle.readline()
        db_fields = parse_line(line,'db_fields')
        if db_fields == '':
            #配置数据异常
            print('error in the second line : ' + line)
            return ''

        #处理db_fields，逗号分隔
        db_filed_list = db_fields.split(',')
        #创建字段定义对象
        for i in range(len(db_filed_list)):
            field_obj = FieldDef()
            field_obj.db_fieldname = db_filed_list[i].strip()
            column_list.append(field_obj)            

        #third line: fields_datatype
        line = fhandle.readline()
        fields_datatype = parse_line(line,'fields_datatype')       
        if fields_datatype == '':
            #配置数据异常
            print('error in the third line : ' + line)
            return ''

        fields_datatype_list = fields_datatype.split(',')
        if len(fields_datatype_list) != len(column_list):
            #个数不匹配
            print('error in the third line,field number is incompatible with the second line.')
            return ''
        
        for i in range(len(column_list)):
            field_obj = column_list[i]
            field_obj.field_datatype = fields_datatype_list[i].strip()

            
        #fourth line: title_fields
        line = fhandle.readline()
        title_fields = parse_line(line,'title_fields')       
        if title_fields == '':
            #配置数据异常
            print('error in the fourth line : ' + line)
            return ''

        title_fields_list = title_fields.split(',')
        if len(title_fields_list) != len(column_list):
            #个数不匹配
            print('error in the fourth line,field number is incompatible with the second line.')
            return ''
        
        for i in range(len(column_list)):
            field_obj = column_list[i]
            field_obj.title_fieldname = title_fields_list[i].strip()
            

        #构造sql 语句的头部
        sql_insert = 'insert into ' + tablename + '(' + db_fields + ') values('

    finally:  
        fhandle.close()

    
    return sql_insert  

def proc_output(output_file,sql_list):
    fhandle = open(output_file, 'w')
    try:  
        for i in range(len(sql_list)):
            thetext = sql_list[i] + '\n';
            fhandle.write(thetext)  
    finally:  
        fhandle.close()
    

reload(sys)
sys.setdefaultencoding('utf8')

#主处理代码
#检查输入文件的存在性
input_file = sys.argv[1]
#输入文件存在性判断
if os.path.exists(input_file) == False:
    print('Input file of ' + input_file + ' does not exist!')
    sys.exit();

if os.path.isfile(input_file) == False:
    print('Input file of ' + input_file + ' does not exist!')
    sys.exit();

config_file = sys.argv[3]    
#配置文件存在性判断
if os.path.exists(config_file) == False:
    print('Config file of ' + config_file + ' does not exist!')
    sys.exit();

if os.path.isfile(config_file) == False:
    print('Config file of ' + config_file + ' does not exist!')
    sys.exit();

output_file = sys.argv[2]
    
#先处理配置文件
#构造sql,并将数据列配置存放在一个列表中
data_column_list = []
sql_insert_head = proc_config_file(config_file,data_column_list)

#处理数据文件，输出结果存入sql_output_list列表中
sql_output_list = []
retValue = proc_input_data(input_file,data_column_list,sql_insert_head,sql_output_list)
if not retValue:
    print('something is wrong!')
    sys.exit()

#输出sql srcipt file
proc_output(output_file,sql_output_list)
print('finished!')
sys.exit()